package com.turing.post.cardmanagement.impl;

import java.math.BigDecimal;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.cardmanagement.util.NonEntityCard;
import com.turing.post.cardmanagement.util.NonEntityCardDao;
import com.turing.post.util.PCPSApplicationException;

@Stateless(mappedName = "cardmanagement.impl.NonEntityCardDaoImpl")
@Remote
@Local
public class NonEntityCardDaoImpl implements NonEntityCardDao {

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public NonEntityCard getNonEntityCardBycardseqno(String cardseqno) {
		return _em.find(NonEntityCard.class, cardseqno);
	}

	@Override
	public Integer deleteNonEntityCard(String Batchno)
			throws PCPSApplicationException {
		Query query = _em
				.createNativeQuery("delete from NonEntityCard where batchno='"
						+ Batchno + "'");
		return query.executeUpdate();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<NonEntityCard> getNonEntityCardListByBatchno(String Batchno) {
		String sql = "select * from NonEntityCard where batchno='" + Batchno
				+ "'";
		Query query = _em.createNativeQuery(sql, NonEntityCard.class);
		return query.getResultList();
	}

	@Override
	public void insertNonEntityCard(NonEntityCard NonEntityCard)
			throws PCPSApplicationException {
		Query query = _em
				.createNativeQuery("insert into NonEntityCard values (?1,?2,?3,?4,?5,?6)");
		query.setParameter(1, NonEntityCard.getBatchNo());
		query.setParameter(2, NonEntityCard.getCardSeqNo());
		query.setParameter(3, NonEntityCard.getCardNo());
		query.setParameter(4, NonEntityCard.getCardPwd());
		query.setParameter(5, NonEntityCard.getTwoTrackContent());
		query.setParameter(6, NonEntityCard.getThirdTrackContent());

		query.executeUpdate();
	}

	/**
	 * @按批次号,起止号段查询库存
	 * @param Batchno
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<NonEntityCard> getNonEntityCardListByBatchnoAndSeqno(
			String Batchno, String startCardSeqno, String endCardSeqno) {
		String sql = "select * from NonEntityCard where batchno=?1 and cardseqno between ?2 and ?3";
		Query query = _em.createNativeQuery(sql, NonEntityCard.class);
		query.setParameter(1, Batchno);
		query.setParameter(2, startCardSeqno);
		query.setParameter(3, endCardSeqno);
		return query.getResultList();
	}

	@Override
	public Integer getNonEntityCardCountByBatchnoAndSeqno(String Batchno,
			String startCardSeqno, String endCardSeqno) {
		String sql = "select nvl(count(*),0) from NonEntityCard where batchno=?1 and cardseqno between ?2 and ?3";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, Batchno);
		query.setParameter(2, startCardSeqno);
		query.setParameter(3, endCardSeqno);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * @按批次号,起止号段删除库存
	 * @param Batchno
	 *            param startCardSeqno param endCardSeqnoString
	 * @return
	 */
	public Integer deleteNonEntityCardListByBatchnoAndSeqno(String Batchno,
			String startCardSeqno, String endCardSeqnoString) {
		Query query = _em
				.createNativeQuery("delete from NonEntityCard where batchno='"
						+ Batchno
						+ "' and to_number(cardseqno) between to_number('"
						+ startCardSeqno + "') and to_number('"
						+ endCardSeqnoString + "')");
		return query.executeUpdate();
	}

	/**
	 * @按批次号,起止号段查询库存数量
	 * @param Batchno
	 * @return
	 */
	@Override
	public Integer getNonEntityCardListCountByBatchno(String Batchno,
			String startCardSeqno, String endCardSeqnoString) {
		String sql = "select count(*) from NonEntityCard where batchno='"
				+ Batchno + "' and to_number(cardseqno) between to_number('"
				+ startCardSeqno + "') and to_number('" + endCardSeqnoString
				+ "')";
		Query query = _em.createNativeQuery(sql);
		return ((BigDecimal) query.getSingleResult()).intValue();
	}
}
